--    Program: 		CAPAREXT_APPROVAL_LIMIT.sql
--    Description:  Extract AR Approval Limit information
--    Revision:     1st     
--    Parameter:    1. set of books short name e.g. SOB_C999

set doc off;      
set verify off;
set serveroutput on size 1000000;
whenever sqlerror exit failure rollback;
alter session set nls_date_format='YYYYMMDD HH24:MI:SS';

DECLARE   
   l_processed_count		NUMBER;
   l_rejected_count 		NUMBER;
   l_err_msg				VARCHAR2(2000);
                    		
   l_utl_file       		utl_file.file_type;
   l_filename       		VARCHAR2(255);
   l_pathname       		VARCHAR2(100);
                    		
   l_ngo_sob				VARCHAR2(20);
   l_ngo_sob_id         	NUMBER;
                    		  
   CURSOR cur_approval_limits (p_sob_name varchar)  IS
      select aul.* 
      from   ar_approval_user_limits aul,
      	     fnd_user fu
      where  aul.user_id = fu.user_id
      and    substr(fu.user_name, 1, 4) = substr(p_sob_name, -4);

BEGIN
   l_pathname := '/tmp';
   l_filename := 'CAPAR_APPROVAL_LIMIT.dat';
   l_utl_file := utl_file.fopen(l_pathname, l_filename, 'w');
   
   l_processed_count := 0;
   l_rejected_count  := 0;
   
   l_ngo_sob := '&1'; 
   
   begin
      select set_of_books_id
      into l_ngo_sob_id
      from gl_sets_of_books
      where upper(short_name) = upper(l_ngo_sob);
   exception
      when others then
        dbms_output.put_line(l_ngo_sob||': set of books name not found');
   end;
   
   FOR cur IN cur_approval_limits(upper(l_ngo_sob)) LOOP  
   
     -- output data
     utl_file.put_line( l_utl_file, 
	   cur.user_id|| chr(9) ||
	   cur.created_by|| chr(9) ||
	   cur.creation_date|| chr(9) ||
	   cur.last_update_date|| chr(9) ||
	   cur.last_updated_by|| chr(9) ||
	   cur.last_update_login|| chr(9) ||
	   cur.amount_to|| chr(9) ||
	   cur.amount_from|| chr(9) ||
	   cur.attribute1|| chr(9) ||
	   cur.attribute2|| chr(9) ||
	   cur.attribute3|| chr(9) ||
	   cur.attribute4|| chr(9) ||
	   cur.attribute5|| chr(9) ||
	   cur.attribute6|| chr(9) ||
	   cur.attribute7|| chr(9) ||
	   cur.attribute8|| chr(9) ||
	   cur.attribute9|| chr(9) ||
	   cur.attribute10|| chr(9) ||
	   cur.ATTRIBUTE_CATEGORY|| chr(9) ||
	   cur.attribute11|| chr(9) ||
	   cur.attribute12|| chr(9) ||
	   cur.attribute13|| chr(9) ||
	   cur.attribute14|| chr(9) ||
	   cur.attribute15|| chr(9) ||
	   cur.CURRENCY_CODE|| chr(9) ||
	   cur.DOCUMENT_TYPE|| chr(9) ||
	   cur.REASON_CODE|| chr(9) ||
	   cur.PRIMARY_FLAG
	 );
     
	 l_processed_count := l_processed_count + 1;
   END LOOP;

   IF l_processed_count = 0 THEN   
     l_rejected_count := l_rejected_count + 1;
     l_err_msg := l_ngo_sob || ': Approval Limit not found';
   END IF;
   
    --insert into log table
    INSERT INTO capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
    VALUES ('CAPAREXT_APPROVAL_LIMIT', SYSDATE, l_processed_count, l_rejected_count, l_processed_count-l_rejected_count, l_err_msg );
   
   COMMIT;

   utl_file.fclose(l_utl_file);
EXCEPTION 
  WHEN OTHERS THEN
    dbms_output.put_line(SQLERRM);
    ROLLBACK;  
END;
/


--exit
